﻿
using System;
using System.Data.SqlClient;
using System.Data;


namespace OPCUA
{
    public class sql
    {
        // Integrated Security=SSPI; 暂时不用
        public static string connectionString = "Initial Catalog=xsqlpool1; Max Pool Size=50; Min Pool Size=1;" 
            + "Data Source=.;Initial Catalog=dbname;User ID=sa;Password=pwd";
        public static string currentErrorMsg {  get; set; }

        //连接池方式操作数据库返回结果类
        public class openResult
        {
            public bool successed { get; set; }
            public System.Data.DataTable dataTable { get; set; }
            public string errmsg { get; set; }
        }

        //连接池方式操作数据库查询
        public static openResult open(string cmmtext)
        {
            openResult sqlResult = new openResult();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    try { 
                        connection.Open(); 
                    }
                    catch (Exception ex)
                    {
                        sqlResult.errmsg = ex.Message; 
                        sqlResult.successed = false;
                        return sqlResult;
                    };

                System.Data.SqlClient.SqlDataAdapter sqlda = new System.Data.SqlClient.SqlDataAdapter(cmmtext, connection);
                System.Data.DataSet sqlds = new DataSet();

                try
                {
                    //缓存查询结果
                    sqlda.Fill(sqlds, "xdata");
                    sqlResult.dataTable = sqlds.Tables["xdata"];
                }
                catch (Exception ex)
                {
                    sqlResult.errmsg = ex.Message;
                    sqlResult.successed = false;
                    return sqlResult;
                }

                sqlResult.successed = true;
                return sqlResult;
            }
            //根据返回结果里面的successed判断查询是否成功

        }

        public static bool exec(string cmmtext)
        {
            //连接字符串 Initial Catalog=xsqlpool1; 中的 xsqlpool1 就是连接池名,不同名就代表新开另一个连接池
            using (SqlConnection connection = new SqlConnection( connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    try { 
                        connection.Open(); 
                    } catch (Exception ex)
                    {
                        //暂不记录错误内容，连接未建立
                        //currentErrorMsg = ex.Message;
                        return false; 
                    };

                try
                {
                    //不缓存查询结果，直接执行
                    new System.Data.SqlClient.SqlCommand(cmmtext, connection).ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    //记录错误内容
                    currentErrorMsg = ex.Message;

                    return false;
                }

                return true;
            }

        }
    }
}
